package com.springapp.dao.dao.impl;

import com.springapp.dao.dao.IArticleDAO;
import com.springapp.model.Article;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
/**
 * Created by nicol_000 on 28/07/14.
 */
@Repository
public class ArticleDAO implements IArticleDAO {
    @Qualifier("dataSource") //Fait référence au datasource dans le mvc-dispatcher.xml
    @Autowired
    private DataSource dataSource;

    @Autowired
    private ParametreDAO param;


    public Article getArticleByID(int _eIDArticle) {

        Article article = null;

        String req = "SELECT art.IDArticle\n" +
                       "\t , art.RefArticle\n" +
                       "\t , art.libelle as 'LibArticle'\n" +
                       "\t , ssc.Libelle\n" +
                       "FROM Article art\n" +
                       "INNER JOIN Art_Classe cl ON cl.IDClasse = art.IDArtClasse\n" +
                       "INNER JOIN Art_SousCategorie ssc ON ssc.IDSousCat = art.IDArt_SousCategorie\n" +
                       "WHERE art.IDArticle = " +_eIDArticle;

        Connection conn=null;
        try {
            conn=dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(req);
            ResultSet result=ps.executeQuery();
            while(result.next()){
                if(result != null)
                {
                    //On génère un nouvel article
                    article = new Article(
                            result.getInt("IDArticle"),
                            result.getString("RefArticle"),
                            result.getString("LibArticle"),
                            result.getString("Libelle")
                    );
                }
            }
            result.close();
            ps.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return article;
    }

    public ArrayList<Article> getListeArticles(boolean _EstPicking){
        //**
        //* Récupération des Articles présents au moins dans 1 palette, qui n'est pas expédiée.
        //* Les alertes stocks picking seront filtrées directement côté client.
        //**
        int iPick;
        if(_EstPicking)
            iPick = 1;
        else
            iPick = 0;
        String req = "SELECT DISTINCT art.IDArticle\n" +
                "    , RefArticle\n" +
                "    , art.libelle\n" +
                "    , volume\n" +
                "    , norme\n" +
                "    , artC.descr as 'Classe'\n" +
                "    , artSCa.Libelle as 'SousCategorie'\n" +
                "    , StockMini\n" +
                "    , StockAlerte\n" +
                "    , StockSecurite\n" +
                "    , StockPickingMini\n" +
                "    , StockPickingAlerte\n" +
                "    , StockPickingSecurite\n" +
                "    , BesoinMoyenQuot\n" +
                "    , BesoinMaxQuot\n" +
                "    , (SELECT ROUND(AVG(CAST(artfou2.DelaisLivraison as float)),0)\n" +
                "        FROM Art_Fournisseur artfou2\n" +
                "        WHERE artfou2.IDArticle = art.IDArticle\n" +
                "        GROUP BY artfou2.IDArticle) as 'Delais_LivraisonMoyen'\n" +
                "    , emp.EstPicking\n" +
                "    , dbo.getTotalByArticle(art.IDArticle,"+iPick+") as 'TotalArticle'\n" +
                "FROM ARTICLE art\n" +
                "INNER JOIN Art_Classe artC ON artC.IDClasse = art.IDArtClasse\n" +
                "INNER JOIN Art_SousCategorie artSCa ON artSCa.IDSousCat = art.IDArt_SousCategorie\n" +
                "INNER JOIN Art_Fournisseur artFou ON artFou.IDArticle = art.IDArticle\n" +
                "INNER JOIN Pal_ArtFou pArtFou ON pArtFou.IDArtFou = artFou.IDArtFournisseur\n" +
                "INNER JOIN Palette p ON p.IDPalette = pArtFou.IDPalette\n" +
                "INNER JOIN Emplacement emp ON emp.IDEmplacement = p.IDEmplacement\n" +
                "WHERE p.IDEtatOperation <> 5\n" +
                "AND emp.EstPicking = "+iPick+"\n" +
                "Group by art.IDArticle, RefArticle, art.libelle, volume, norme, artC.descr, artSCa.Libelle, StockMini, StockSecurite, StockAlerte, BesoinMoyenQuot, BesoinMaxQuot, emp.EstPicking, emp.qteActuelleZoneStock, pArtFou.QteArticle,StockPickingMini,StockPickingSecurite,StockPickingAlerte \n" +
                "\n";

        Connection conn=null;
        ArrayList<Article> lstArticles = new ArrayList<Article>();

        try {
            conn=dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(req);
            ResultSet result=ps.executeQuery();
            while(result.next()){
                if(result != null)
                {
                    //On génère un nouvel article
                    Article article = new Article(

                        result.getInt("IDArticle"),
                        result.getString("RefArticle"),
                        result.getString("libelle"),
                        result.getFloat("volume"),
                        result.getString("norme"),
                        result.getString("Classe"),
                        result.getString("SousCategorie"),
                        result.getInt("BesoinMoyenQuot"),
                        result.getInt("BesoinMaxQuot"),
                        result.getInt("Delais_LivraisonMoyen"),
                        result.getFloat("StockMini"),
                        result.getFloat("StockAlerte"),
                        result.getFloat("StockSecurite"),
                        result.getInt("TotalArticle"),
                        result.getFloat("StockPickingMini"),
                        result.getFloat("StockPickingAlerte"),
                        result.getFloat("StockPickingSecurite")
                    );

                    lstArticles.add(article);
                }
            }
            result.close();
            ps.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return lstArticles;
    }


    @Override
    public void insert(Article obj) {

    }

    @Override
    public void delete(Article obj) {

    }

    @Override
    public String updatePicking(ArrayList<Article> lstArticleAlertes) {
        String sUpdateOk = "";
        int eUpdateOk = 0;


        float fStockMini =0, fStockSecu=0, fStockalerte = 0;
        String sCaseStockMini= " SET StockPickingMini  = CASE IDArticle";
        String sCaseStockSecu= ", StockPickingAlerte = CASE IDArticle";
        String sCaseStockAler= ", StockPickingSecurite  = CASE IDArticle";
        String sIDArticle = "WHERE IDArticle IN (";

        String reqUpdate =  "UPDATE ARTICLE\n";


        for(Article art : lstArticleAlertes)
        {
            sIDArticle += art.geteIDArticle() +", ";
            fStockMini   = art.geteStockMiniPicking(art.geteBesoinQuotidienMoy(), param.getDelaisJourSecurite() );
            sCaseStockMini += "\t\t\tWHEN "+art.geteIDArticle()+" THEN "+fStockMini+" \n ";
            art.seteStockMini(fStockMini);
        }


        reqUpdate += sCaseStockMini
                + " END \n";

        for(Article art : lstArticleAlertes)
        {
            fStockalerte = art.geteStockAlertePicking(art.geteBesoinQuotidienMoy(), art.geteDelaisLivraisonMoyen());
            sCaseStockAler += "\t\t\tWHEN "+art.geteIDArticle()+" THEN "+fStockalerte+" \n ";
            art.seteStockAlerte(fStockalerte);
        }

        reqUpdate += sCaseStockAler +"\n"
                + " END \n";


        for(Article art : lstArticleAlertes)
        {

            fStockSecu   = art.geteStockSecuritePicking(art.geteStockMini(),art.geteStockSecurite());
            sCaseStockSecu += "\t\t\t WHEN "+art.geteIDArticle()+" THEN "+fStockSecu+" \n ";
            art.seteStockSecurite(fStockSecu);
        }
        sIDArticle = sIDArticle.substring(0,sIDArticle.length()-2);

        reqUpdate += sCaseStockSecu +"\n"
                + " END\n"
                + sIDArticle + ")";


        Connection conn = null;
        try {
            conn=dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(reqUpdate);
            eUpdateOk = ps.executeUpdate();
            //**
            //* La méthode Update retourne le nombre de ligne affectée.
            //* Si le nombre de ligne affectées = le nombre d'article dans ma liste, l'update c'est correctement déroulé.
            //**

            ps.close();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }

        if (eUpdateOk >= 1)
        {
            sUpdateOk = "La mise à jour des stocks terminée avec succès ! -- Nombre de lignes affectées : " + eUpdateOk;
        }else
        {
            sUpdateOk = "Erreur durant la mise à jour des stocks. Requête : " + reqUpdate;
        }


        return sUpdateOk;

    }

    @Override
    public String update(ArrayList<Article> lstArticleAlertes) {
        String sUpdateOk = "";
        int eUpdateOk = 0;


        float fStockMini =0, fStockSecu=0, fStockalerte = 0;
        String sCaseStockMini= " SET StockMini  = CASE IDArticle";
        String sCaseStockAler= ", StockAlerte = CASE IDArticle";
        String sCaseStockSecu= ", StockSecurite  = CASE IDArticle";
        String sIDArticle = "WHERE IDArticle IN (";

        String reqUpdate =  "UPDATE ARTICLE\n";


        for(Article art : lstArticleAlertes)
        {
            sIDArticle += art.geteIDArticle() +", ";
            fStockMini   = art.geteStockMini(art.geteBesoinQuotidienMoy(), param.getDelaisJourSecurite() );
            sCaseStockMini += "\t\t\tWHEN "+art.geteIDArticle()+" THEN "+fStockMini+" \n ";
            art.seteStockMini(fStockMini);
        }


        reqUpdate += sCaseStockMini
                + " END \n";

        for(Article art : lstArticleAlertes)
        {
            fStockalerte = art.geteStockAlerte(art.geteBesoinQuotidienMoy(), art.geteDelaisLivraisonMoyen());
            sCaseStockAler += "\t\t\tWHEN "+art.geteIDArticle()+" THEN "+fStockalerte+" \n ";
            art.seteStockAlerte(fStockalerte);
        }

        reqUpdate += sCaseStockAler +"\n"
                + " END \n";


        for(Article art : lstArticleAlertes)
        {

            fStockSecu   = art.geteStockSecurite(art.geteStockMini(),art.geteStockAlerte());
            sCaseStockSecu += "\t\t\t WHEN "+art.geteIDArticle()+" THEN "+fStockSecu+" \n ";
            art.seteStockSecurite(fStockSecu);
        }
        sIDArticle = sIDArticle.substring(0,sIDArticle.length()-2);

        reqUpdate += sCaseStockSecu +"\n"
                + " END\n"
                + sIDArticle + ")";


        Connection conn = null;
        try {
            conn=dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(reqUpdate);
            eUpdateOk = ps.executeUpdate();
            //**
            //* La méthode Update retourne le nombre de ligne affectée.
            //* Si le nombre de ligne affectées = le nombre d'article dans ma liste, l'update c'est correctement déroulé.
            //**

            ps.close();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }

        if (eUpdateOk >= 1)
        {
            sUpdateOk = "La mise à jour des stocks terminée avec succès ! -- Nombre de lignes affectées : " + eUpdateOk;
        }else
        {
            sUpdateOk = "Erreur durant la mise à jour des stocks. Requête : " + reqUpdate;
        }


        return sUpdateOk;

    }



}
